#!/usr/bin/perl

use DBI;
use strict;

# this script goes through the fluxdata table and finds entries that were processed with
# what are now considered to be bad flux calibration or bandpass calibration, and sets them
# as unprocessed again

# get our command line arguments
my $output_debug=0;
for (my $i=0;$i<=$#ARGV;$i++){
    if ($ARGV[$i] eq "-D"){
	# level up debug printing
	$output_debug++;
    }
}

# database details
my $dsn="dbi:Pg:dbname=atca;host=mentok;port=5432";
my $user="postgres";
my $password="";

# Setup the database connection
my $dbh=DBI->connect($dsn,$user,$password,{ RaiseError => 0, AutoCommit => 1});

# first, find all the entries that have required flux calibration
my $fnd_query="SELECT id,num_observations,observation_ids,fluxcal_id FROM fluxdata ".
    "WHERE processed_as_flux=false LIMIT 1";
if ($output_debug>0){
    print "executing query: $fnd_query\n";
}
my $fnd=$dbh->prepare($fnd_query);
$fnd->execute;

while (my @fnd_response=$fnd->fetchrow){
    my ($id,$numobs,$obsidsptr,$fluxid)=@fnd_response;
#    my @obsids=@{$obsidsptr};
    my @obsids=&postgresql_array_get($obsidsptr);
    my $chk_query="SELECT quality_flag FROM fluxdata WHERE id=$fluxid";
    if ($output_debug>0){
	print "executing query: $chk_query\n";
    }
    my $chk=$dbh->prepare($chk_query);
    $chk->execute;
    my $qualflag;
    while (my @chk_response=$chk->fetchrow){
	$qualflag=$chk_response[0];
    }
    $chk->finish;
    if ($output_debug>0){
	print "got quality flag of $qualflag\n";
    }

    if ($qualflag==0){
	# this data is bad
	# we set it as such
	my $stb_query="UPDATE fluxdata SET quality_flag=false WHERE id=$id";
	if ($output_debug>0){
	    print "executing query: $stb_query\n";
	}
#	my $stb=$dbh->prepare($stb_query);
#	$stb->execute;
#	$stb->finish;

	# we remove this observation from the processed details of its parent
	# datasets
	for (my $i=0;$i<=$#obsids;$i++){
	    my $qto_query="SELECT num_processed,processed_ids FROM observation ".
		"WHERE id=$obsids[$i]";
	    my $qto=$dbh->prepare($qto_query);
	    $qto->execute;
	    my ($nproc,$procidsptr);
	    my @procids;
	    while (my @qto_response=$qto->fetchrow){
		($nproc,$procidsptr)=@qto_response;
#		@procids=@{$procidsptr};
		@procids=&postgresql_array_get($procidsptr);
		for (my $j=0;$j<=$#procids;$j++){
		    if ($procids[$j]==$id){
			splice @procids,$j,1;
			$j--;
			$nproc--;
		    }
		}
	    }
	    $qto->finish;
	    my $upo_query="UPDATE observation SET (num_processed,processed_ids)=".
		"(".$nproc.",'".&array_to_text(@procids)."') WHERE id=".$obsids[$i];
	    if ($output_debug>0){
		print "executing query: $upo_query\n";
	    }
#	    my $upo=$dbh->prepare($upo_query);
#	    $upo->execute;
#	    $upo->finish;
	}
    }
}
$fnd->finish;


$dbh->disconnect;

sub postgresql_array_get {
    my ($arraystring)=@_;
    # turns a postgresql array string into a perl array
    if ($output_debug>2){
	print "attempting to turn $arraystring into array\n";
    }
    $arraystring=~s/[\{\}]//g;
    my @els=split(/\,/,$arraystring);
    return @els;
}

sub array_to_text {
    # turns an array into a postgresql compliant input
    my (@array)=@_;

    my $string="{";
    # check for letters, but we don't look for 'E' which may be used
    # in exponential numbers
    my $needsquotes=0;
    for (my $i=0;$i<=$#array;$i++){
	if ($array[$i]=~/[a-zA-DF-Z]/){
	    $needsquotes=1;
	}
    }
    for (my $i=0;$i<=$#array;$i++){
	if ($i>0){
	    $string.=",";
	}
	if ($needsquotes==1){
	    $string.="'";
	}
	if (!$array[$i]){
	    if ($needsquotes==1){
		$string.="NA";
	    } else {
		$string.="999";
	    }
	} else {
	    $string.=$array[$i];
	}
	if ($needsquotes==1){
	    $string.="'";
	}
    }
    $string.="}";
    return $string;
}
